﻿using System.Data;
using System.Data.SqlClient;

#region DBAcess

/// <summary>
/// This class is used for connect to DB and excute the sql Statement
/// </summary>
public class DBAcess
{
    /// <summary>
    /// Connect string to DB
    /// </summary>
    /// <returns></returns>
    public static string constring()
    {
        //string strcn = @"Data Source=HW-HUBIAN-1\JILKA;Initial Catalog=Orchard;Persist Security Info=True;User ID=sa;Password='Jilka$0817';Pooling=true";
        string strcn = @"Data Source=.\SQLEXPRESS;Initial Catalog=Orchard;Integrated Security=True;";
        return strcn;
    }

    /// <summary>
    /// open the connection
    /// </summary>
    /// <returns></returns>
    public static SqlConnection returncon()
    {
        string sql = constring();
        SqlConnection con;
        try
        {
            con = new SqlConnection(sql);
        }
        catch
        {
            con = null;
            return con;
        }
        return con;
    }

    /// <summary>
    /// Close the connection
    /// </summary>
    /// <param name="con"></param>
    public static void conclose(SqlConnection con)
    {
        if (con.State.ToString() == "Open")
        {
            con.Close();
        }
    }


    /// <summary>
    /// Select data from data base
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable SelectFromDB(string sql)
    {
        DataTable table = null;
        SqlConnection mycon = returncon();
        if (mycon == null)
        {
            return table;
        }
        try
        {
            table = new DataTable();
            SqlCommand mycommand = new SqlCommand(sql, mycon);
            SqlDataAdapter myad = new SqlDataAdapter();
            myad.SelectCommand = mycommand;
            mycon.Open();
            myad.Fill(table);
        }
        catch
        {
            table = null;
            return table;
        }
        finally
        {
            mycon.Close();
            mycon.Dispose();
        }
        return table;
    }


    /// <summary>
    /// Execute sql ,if find exception ,rollback
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int ExecuteSQLWithRollback(string sql)
    {
        SqlConnection mycon = returncon();
        mycon.Open();
        SqlTransaction mytran = mycon.BeginTransaction();
        SqlCommand mycommand = new SqlCommand(sql, mycon);
        mycommand.Transaction = mytran;
        try
        {
            mycommand.ExecuteNonQuery();
        }
        catch
        {
            mytran.Rollback();
            DBAcess.conclose(mycon);
            return 0;
        }
        mytran.Commit();
        DBAcess.conclose(mycon);
        return 1;
    }

    /// <summary>
    /// Execute sql without roll back module
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int ExecuteSQL(string sql)
    {
        SqlConnection mycon = returncon();
        mycon.Open();
        //SqlTransaction mytran = mycon.BeginTransaction();
        SqlCommand mycommand = new SqlCommand(sql, mycon);
        //mycommand.Transaction = mytran;
        try
        {
            mycommand.ExecuteNonQuery();
        }
        catch
        {
            //mytran.Rollback();
            DBAcess.conclose(mycon);
            return 0;
        }
        //mytran.Commit();
        DBAcess.conclose(mycon);
        return 1;
    }
}

#endregion